using System;
using System.Collections;
using System.Data.SqlClient;
using System.Data;
using System.Text;
using System.Runtime.InteropServices;
using System.Reflection;
using System.IO;
using System.Collections.Generic;
using System.Globalization;

namespace PROJECTNAMESPACE
{
	internal class SqlServers
	{
		#region database discovery
		[DllImport("odbc32.dll")]
		private static extern short SQLAllocHandle(short hType, IntPtr inputHandle, out IntPtr outputHandle);
		[DllImport("odbc32.dll")]
		private static extern short SQLSetEnvAttr(IntPtr henv, int attribute, IntPtr valuePtr, int strLength);
		[DllImport("odbc32.dll")]
		private static extern short SQLFreeHandle(short hType, IntPtr handle);
		[DllImport("odbc32.dll", CharSet = CharSet.Ansi)]
		private static extern short SQLBrowseConnect(IntPtr hconn, StringBuilder inString,
			short inStringLength, StringBuilder outString, short outStringLength,
			out short outLengthNeeded);

		private const short SQL_HANDLE_ENV = 1;
		private const short SQL_HANDLE_DBC = 2;
		private const int SQL_ATTR_ODBC_VERSION = 200;
		private const int SQL_OV_ODBC3 = 3;
		private const short SQL_SUCCESS = 0;

		private const short SQL_NEED_DATA = 99;
		private const short DEFAULT_RESULT_SIZE = 1024;
		private const string SQL_DRIVER_STR = "DRIVER=SQL SERVER";

		public enum SQLServerTypeConstants
		{
			SQL2005 = 0,
			SQL2008 = 1,
			SQLAzure = 2,
		}

		public static SQLServerTypeConstants DatabaseVersion = SQLServerTypeConstants.DATABASETYPE;

		private SqlServers()
		{
		}

		internal static string[] GetServers()
		{
			string[] retval = null;

			var txt = string.Empty;
			var henv = IntPtr.Zero;
			var hconn = IntPtr.Zero;
			var inString = new StringBuilder(SQL_DRIVER_STR);
			var outString = new StringBuilder(DEFAULT_RESULT_SIZE);
			var inStringLength = (short)inString.Length;
			var lenNeeded = (short)0;

			try
			{
				if (SQL_SUCCESS == SQLAllocHandle(SQL_HANDLE_ENV, henv, out henv))
				{
					if (SQL_SUCCESS == SQLSetEnvAttr(henv, SQL_ATTR_ODBC_VERSION, (IntPtr)SQL_OV_ODBC3, 0))
					{
						if (SQL_SUCCESS == SQLAllocHandle(SQL_HANDLE_DBC, henv, out hconn))
						{
							if (SQL_NEED_DATA == SQLBrowseConnect(hconn, inString, inStringLength, outString,
								DEFAULT_RESULT_SIZE, out lenNeeded))
							{
								if (DEFAULT_RESULT_SIZE < lenNeeded)
								{
									outString.Capacity = lenNeeded;
									if (SQL_NEED_DATA != SQLBrowseConnect(hconn, inString, inStringLength, outString,
										lenNeeded, out lenNeeded))
									{
										throw new ApplicationException("Unabled to aquire SQL Servers from ODBC driver.");
									}
								}
								txt = outString.ToString();
								int start = txt.IndexOf("{") + 1;
								int len = txt.IndexOf("}") - start;
								if ((start > 0) && (len > 0))
								{
									txt = txt.Substring(start, len);
								}
								else
								{
									txt = string.Empty;
								}
							}
						}
					}
				}
			}
			catch (Exception ex)
			{
				throw ex;
			}
			finally
			{
				if (hconn != IntPtr.Zero)
				{
					SQLFreeHandle(SQL_HANDLE_DBC, hconn);
				}
				if (henv != IntPtr.Zero)
				{
					SQLFreeHandle(SQL_HANDLE_ENV, hconn);
				}
			}

			if (txt.Length > 0)
			{
				retval = txt.Split(",".ToCharArray());
			}
			return retval;
		}

		internal static bool TestConnectionString(string connectString)
		{
			bool valid = false;
			System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection();
			try
			{
				conn.ConnectionString = connectString;
				conn.Open();
				valid = true;
			}
			catch (Exception ex)
			{
				System.Diagnostics.Debug.WriteLine(ex.ToString());
				valid = false;
			}
			finally
			{
				conn.Close();
			}
			return valid;
		}

		internal static List<HistoryItem> GetHistory(string connectionString)
		{
			var retval = new List<HistoryItem>();
			var settings = new nHydrateSetting();
			if (CanUseExtendedProperty(connectionString))
			{
				var historyRow = SqlServers.SelectExtendedProperty(connectionString, "History", string.Empty, string.Empty, string.Empty);
				settings.LoadHistory(historyRow);
				return settings.History;
			}

			if (settings.History.Count == 0)
			{
				settings = new nHydrateSetting();
				settings.Load(connectionString);
			}
			return settings.History;

		}

		internal static string BuildConnectionString(bool integratedSecurity, string databaseName, string serverName, string userName, string password)
		{

			StringBuilder connStr = new StringBuilder();
			//ODBCSTUFF
			//Provider=SQLOLEDB.1;Use Procedure for Prepare=1;Auto Translate=True;Use Encryption for Data=False;Tag with column collation when possible=False
			if (integratedSecurity)
			{
				connStr.Append("Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=");
				connStr.Append(databaseName);
				connStr.Append(";Data Source=");
				connStr.Append(serverName);
				connStr.Append(";Packet Size=4096;Workstation ID=");
				connStr.Append(serverName);
			}
			//ODBC STUFF
			//Provider=SQLOLEDB.1;Use Procedure for Prepare=1;Auto Translate=True;Use Encryption for Data=False;Tag with column collation when possible=False
			else
			{
				connStr.Append("Persist Security Info=False;User ID=");
				connStr.Append(userName);
				connStr.Append(";PWD=");
				connStr.Append(password);
				connStr.Append(";Initial Catalog=");
				connStr.Append(databaseName);
				connStr.Append(";Data Source=");
				connStr.Append(serverName);
				connStr.Append(";Packet Size=4096;Workstation ID=");
				connStr.Append(serverName);
			}

			return connStr.ToString();

		}

		internal static string[] GetDatabaseNames(string connectString)
		{
			var databaseNames = new ArrayList();
			System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection();
			SqlDataReader databaseReader = null;
			SqlDataReader existsReader = null;

			try
			{
				conn.ConnectionString = connectString;
				conn.Open();

				var cmdDatabases = new SqlCommand();
				cmdDatabases.CommandText = "use [master] select name from sysdatabases";
				cmdDatabases.CommandType = System.Data.CommandType.Text;
				cmdDatabases.Connection = conn;
				databaseReader = cmdDatabases.ExecuteReader();
				while (databaseReader.Read())
				{
					databaseNames.Add(databaseReader["name"]);
				}
			}
			catch (Exception ex)
			{
				System.Diagnostics.Debug.WriteLine(ex.ToString());
				databaseNames.Clear();
			}
			finally
			{
				if (databaseReader != null)
					databaseReader.Close();
				if (conn != null)
					conn.Close();
			}

			var itemsToRemove = new ArrayList();
			foreach (string dbName in databaseNames)
			{
				try
				{
					conn.Open();
					var cmdUserExist = new SqlCommand();
					cmdUserExist.CommandText = "use [" + dbName + "] select case when Permissions()&254=254 then 1 else 0 end as hasAccess";
					cmdUserExist.CommandType = System.Data.CommandType.Text;
					cmdUserExist.Connection = conn;
					existsReader = cmdUserExist.ExecuteReader();
					if (existsReader.Read())
					{
						try
						{
							if (int.Parse(existsReader["hasAccess"].ToString()) == 0)
							{
								itemsToRemove.Add(dbName);
							}
						}
						catch (Exception ex)
						{
							System.Diagnostics.Debug.WriteLine(ex.ToString());
						}
					}
				}
				catch (Exception ex)
				{
					System.Diagnostics.Debug.WriteLine(ex.ToString());
					itemsToRemove.Add(dbName);
				}
				finally
				{
					if (existsReader != null)
						existsReader.Close();
					if (conn != null)
						conn.Close();
				}
			}
			foreach (string removedItem in itemsToRemove)
			{
				databaseNames.Remove(removedItem);
			}

			return (string[])databaseNames.ToArray(typeof(string));
		}

		internal static bool HasCreatePermissions(string connectString)
		{
			bool returnVal = false;
			System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection();
			SqlDataReader existsReader = null;
			try
			{
				conn.ConnectionString = connectString;
				conn.Open();
				var cmdUserExist = new SqlCommand();
				cmdUserExist.CommandText = "use [master] select case when Permissions()&1=1 then 1 else 0 end as hasAccess";
				cmdUserExist.CommandType = System.Data.CommandType.Text;
				cmdUserExist.Connection = conn;
				existsReader = cmdUserExist.ExecuteReader();
				if (existsReader.Read())
				{
					try
					{
						if (int.Parse(existsReader["hasAccess"].ToString()) == 1)
						{
							returnVal = true;
						}
					}
					catch (Exception ex)
					{
						System.Diagnostics.Debug.WriteLine(ex.ToString());
					}
				}
			}
			catch (Exception ex)
			{
				System.Diagnostics.Debug.WriteLine(ex.ToString());
			}
			finally
			{
				if (existsReader != null)
					existsReader.Close();
				if (conn != null)
					conn.Close();
			}
			return returnVal;
		}
		#endregion

		#region create database
		internal static void CreateDatabase(string connectString, string databaseName)
		{
			System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection();
			try
			{
				conn.ConnectionString = connectString;
				conn.Open();
				var cmdCreateDb = new SqlCommand();
				var collate = "%COLLATE%";
				if (!string.IsNullOrEmpty(collate)) collate = " COLLATE " + collate;
				cmdCreateDb.CommandText = "CREATE DATABASE " + databaseName + collate;
				cmdCreateDb.CommandType = System.Data.CommandType.Text;
				cmdCreateDb.Connection = conn;
				cmdCreateDb.ExecuteNonQuery();
			}
			catch { throw; }
			finally
			{
				if (conn != null)
					conn.Close();
			}
		}
		#endregion

		#region database table operations
		internal static void RemoveTable(string connectString, string tableName)
		{
			System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection();
			try
			{
				conn.ConnectionString = connectString;
				conn.Open();
				SqlCommand cmdCreateDb = new SqlCommand();
				if (GetTableNamesAsArrayList(connectString).Contains(tableName))
				{
					cmdCreateDb.CommandText = SqlRemoveTable(tableName);
				}
				else
				{
					return;
				}
				cmdCreateDb.CommandType = System.Data.CommandType.Text;
				cmdCreateDb.Connection = conn;
				cmdCreateDb.ExecuteNonQuery();
			}
			catch (Exception ex)
			{
				throw ex;
			}
			finally
			{
				if (conn != null)
					conn.Close();
			}
		}

		internal static string[] GetTables(string connectString)
		{
			ArrayList databaseTables = GetTableNamesAsArrayList(connectString);
			return (string[])databaseTables.ToArray(typeof(string));
		}

		internal static ArrayList GetTableNamesAsArrayList(string connectString)
		{
			ArrayList databaseTables = new ArrayList();
			SqlDataReader tableReader = null;
			System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection();
			try
			{
				conn.ConnectionString = connectString;
				conn.Open();
				SqlCommand cmdCreateDb = new SqlCommand();
				cmdCreateDb.CommandText = "select name from sysobjects where xtype = 'U' and name <> 'dtproperties'";
				cmdCreateDb.CommandType = System.Data.CommandType.Text;
				cmdCreateDb.Connection = conn;
				tableReader = cmdCreateDb.ExecuteReader();
				while (tableReader.Read())
				{
					databaseTables.Add(tableReader.GetString(0));
				}
			}
			catch (Exception ex)
			{
				throw ex;
			}
			finally
			{
				if (tableReader != null)
					tableReader.Close();
				if (conn != null)
					conn.Close();
			}
			return databaseTables;
		}
		#endregion

		#region database column operations
		internal static DataSet GetTableColumns(string connectString, string tableName)
		{
			var conn = new SqlConnection();
			var cmd = new SqlCommand();
			DataSet tableColumns = new DataSet();
			SqlDataAdapter da = new SqlDataAdapter();

			try
			{
				conn.ConnectionString = connectString;
				cmd.CommandText = GetSqlColumnsForTable(tableName);
				cmd.CommandType = System.Data.CommandType.Text;
				cmd.Connection = conn;
				da.SelectCommand = cmd;
				da.Fill(tableColumns);
			}
			catch (Exception ex)
			{
				throw ex;
			}
			finally
			{
				if (conn != null)
					conn.Close();
			}
			return tableColumns;
		}

		public static bool HasLength(System.Data.SqlDbType dataType)
		{
			if (dataType == System.Data.SqlDbType.BigInt)
				return false;
			else if (dataType == System.Data.SqlDbType.Bit)
				return false;
			else if (dataType == System.Data.SqlDbType.DateTime)
				return false;
			else if (dataType == System.Data.SqlDbType.Decimal)
				return false;
			else if (dataType == System.Data.SqlDbType.Float)
				return false;
			else if (dataType == System.Data.SqlDbType.Image)
				return false;
			else if (dataType == System.Data.SqlDbType.Int)
				return false;
			else if (dataType == System.Data.SqlDbType.Money)
				return false;
			else if (dataType == System.Data.SqlDbType.Real)
				return false;
			else if (dataType == System.Data.SqlDbType.SmallDateTime)
				return false;
			else if (dataType == System.Data.SqlDbType.SmallInt)
				return false;
			else if (dataType == System.Data.SqlDbType.SmallMoney)
				return false;
			else if (dataType == System.Data.SqlDbType.Timestamp)
				return false;
			else if (dataType == System.Data.SqlDbType.TinyInt)
				return false;
			else if (dataType == System.Data.SqlDbType.UniqueIdentifier)
				return false;
			else
				return true;
		}

		#endregion

		#region extended property helpers
		internal static void UpdateDatabaseMetaProperty(string connectionString, string propertyName, string propertyValue)
		{
			//if (DatabaseVersion == SQLServerTypeConstants.SQLAzure)
			//{
			System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection();
			try
			{
				var settings = new nHydrateSetting();
				settings.Load(connectionString);
				switch (propertyName)
				{
					case "dbVersion":
						settings.dbVersion = propertyValue;
						break;
					case "LastUpdate":
						settings.LastUpdate = DateTime.Parse(propertyValue);
						break;
					case "ModelKey":
						settings.ModelKey = new Guid(propertyValue);
						break;
					case "History":
						settings.LoadHistory(propertyValue);
						break;
					default:
						throw new Exception("No property found!");
				}
				settings.Save(connectionString);
			}
			catch (Exception ex)
			{
				throw;
			}
			finally
			{
				if (conn != null)
					conn.Close();
			}
			//} //Azure

			//We no longer use extended properties
			if (CanUseExtendedProperty(connectionString))
			{
				if (ExtendedPropertyExists(connectionString, propertyName, string.Empty, string.Empty, string.Empty))
				{
					//UpdateExtendedProperty(connectionString, propertyName, propertyValue, string.Empty, string.Empty, string.Empty);
					DeleteExtendedProperty(connectionString, propertyName);
				}
				else
				{
					//InsertExtendedPropery(connectionString, propertyName, propertyValue, string.Empty, string.Empty, string.Empty);
				}
			}

		}

		internal static string GetDatabaseMetaProperty(string connectionString, string propertyName)
		{
			if (CanUseExtendedProperty(connectionString))
			{
				return SelectExtendedProperty(connectionString, propertyName, string.Empty, string.Empty, string.Empty);
			}
			else
			{
				var settings = new nHydrateSetting();
				settings.Load(connectionString);
				switch (propertyName)
				{
					case "dbVersion":
						return settings.dbVersion;
					case "LastUpdate":
						return settings.LastUpdate.ToString("yyyy-MM-dd HH:mm:ss");
					case "ModelKey":
						return settings.ModelKey.ToString();
					case "History":
						return settings.ToHistoryString();
					default:
						throw new Exception("No property found!");
				}
			}
		}
		#endregion

		#region extended property private
		private static bool? extendedPropertyEnabled = null;

		/// <summary>
		/// Determines if we can use extended properties, Azure does NOT allow them
		/// </summary>
		private static bool CanUseExtendedProperty(string connectionString)
		{
			if (extendedPropertyEnabled == null)
			{
				System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection();
				try
				{
					conn.ConnectionString = connectionString;
					conn.Open();
					SqlCommand cmdGetExtProp = new SqlCommand();
					cmdGetExtProp.CommandText = "SELECT value FROM ::fn_listextendedproperty('', '', '', '', '', '', '')";
					cmdGetExtProp.CommandType = System.Data.CommandType.Text;
					cmdGetExtProp.Connection = conn;
					cmdGetExtProp.ExecuteNonQuery();
					extendedPropertyEnabled = true;
				}
				catch (Exception ex)
				{
					extendedPropertyEnabled = false;
				}
				finally
				{
					if (conn != null)
						conn.Close();
				}
			}
			return extendedPropertyEnabled.Value;
		}

		internal static void DeleteExtendedProperty(string connectionString, string propertyName)
		{
			//If the database supports extended properties then use them no matter what
			if (CanUseExtendedProperty(connectionString) && ExtendedPropertyExists(connectionString, propertyName, string.Empty, string.Empty, string.Empty))
			{
				System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection();
				try
				{
					conn.ConnectionString = connectionString;
					conn.Open();
					SqlCommand cmdGetExtProp = new SqlCommand();
					cmdGetExtProp.CommandText = String.Format("EXEC sp_dropextendedproperty '{0}'", new object[] { propertyName });
					cmdGetExtProp.CommandType = System.Data.CommandType.Text;
					cmdGetExtProp.Connection = conn;
					cmdGetExtProp.ExecuteNonQuery();
				}
				catch (Exception ex)
				{
					throw ex;
				}
				finally
				{
					if (conn != null)
						conn.Close();
				}
			}
		}

		private static void UpdateExtendedProperty(string connectionString, string property, string propertyValue, string user, string table, string column)
		{
			//If the database supports extended properties then use them no matter what
			if (CanUseExtendedProperty(connectionString))
			{
				string userName = string.Empty;
				string userValue = string.Empty;
				string tableName = string.Empty;
				string tableValue = string.Empty;
				string columnName = string.Empty;
				string columnValue = string.Empty;

				property = "'" + property + "'";
				propertyValue = "'" + propertyValue + "'";
				if (user == string.Empty)
				{
					userName = "NULL";
					userValue = "NULL";
				}
				else
				{
					userName = "'user'";
					userValue = "'" + user + "'";
				}
				if (table == string.Empty)
				{
					tableName = "NULL";
					tableValue = "NULL";
				}
				else
				{
					tableName = "'table'";
					tableValue = "'" + table + "'";
				}
				if (column == string.Empty)
				{
					columnName = "NULL";
					columnValue = "NULL";
				}
				else
				{
					columnName = "'column'";
					columnValue = "'" + column + "'";
				}

				System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection();
				try
				{
					conn.ConnectionString = connectionString;
					conn.Open();
					SqlCommand cmdGetExtProp = new SqlCommand();
					cmdGetExtProp.CommandText = String.Format("EXEC sp_updateextendedproperty {0}, {1}, {2}, {3}, {4}, {5}, {6}, {7}", new object[] { property, propertyValue, userName, userValue, tableName, tableValue, columnName, columnValue });
					cmdGetExtProp.CommandType = System.Data.CommandType.Text;
					cmdGetExtProp.Connection = conn;
					cmdGetExtProp.ExecuteNonQuery();
				}
				catch (Exception ex)
				{
					throw ex;
				}
				finally
				{
					if (conn != null)
						conn.Close();
				}
			}
		}

		private static void InsertExtendedPropery(string connectionString, string property, string propertyValue, string user, string table, string column)
		{
			//If the database supports extended properties then use them no matter what
			if (CanUseExtendedProperty(connectionString))
			{
				string userName = string.Empty;
				string userValue = string.Empty;
				string tableName = string.Empty;
				string tableValue = string.Empty;
				string columnName = string.Empty;
				string columnValue = string.Empty;

				property = "'" + property + "'";
				propertyValue = "'" + propertyValue + "'";
				if (user == string.Empty)
				{
					userName = "NULL";
					userValue = "NULL";
				}
				else
				{
					userName = "'user'";
					userValue = "'" + user + "'";
				}
				if (table == string.Empty)
				{
					tableName = "NULL";
					tableValue = "NULL";
				}
				else
				{
					tableName = "'table'";
					tableValue = "'" + table + "'";
				}
				if (column == string.Empty)
				{
					columnName = "NULL";
					columnValue = "NULL";
				}
				else
				{
					columnName = "'column'";
					columnValue = "'" + column + "'";
				}

				System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection();
				try
				{
					conn.ConnectionString = connectionString;
					conn.Open();
					SqlCommand cmdGetExtProp = new SqlCommand();
					cmdGetExtProp.CommandText = String.Format("EXEC sp_addextendedproperty {0}, {1}, {2}, {3}, {4}, {5}, {6}, {7}", new object[] { property, propertyValue, userName, userValue, tableName, tableValue, columnName, columnValue });
					cmdGetExtProp.CommandType = System.Data.CommandType.Text;
					cmdGetExtProp.Connection = conn;
					cmdGetExtProp.ExecuteNonQuery();
				}
				catch (Exception ex)
				{
					throw ex;
				}
				finally
				{
					if (conn != null)
						conn.Close();
				}
			}

		}

		//SELECT value FROM ::fn_listextendedproperty('companyName', NULL, NULL, NULL, NULL, NULL, NULL)
		internal static string SelectExtendedProperty(string connectionString, string property, string user, string table, string column)
		{
			string returnVal = string.Empty;
			string userName = string.Empty;
			string userValue = string.Empty;
			string tableName = string.Empty;
			string tableValue = string.Empty;
			string columnName = string.Empty;
			string columnValue = string.Empty;

			property = "'" + property + "'";
			if (user == string.Empty)
			{
				userName = "NULL";
				userValue = "NULL";
			}
			else
			{
				userName = "'user'";
				userValue = "'" + user + "'";
			}
			if (table == string.Empty)
			{
				tableName = "NULL";
				tableValue = "NULL";
			}
			else
			{
				tableName = "'table'";
				tableValue = "'" + table + "'";
			}
			if (column == string.Empty)
			{
				columnName = "NULL";
				columnValue = "NULL";
			}
			else
			{
				columnName = "'column'";
				columnValue = "'" + column + "'";
			}

			System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection();
			System.Data.SqlClient.SqlDataReader externalReader = null;
			try
			{
				conn.ConnectionString = connectionString;
				conn.Open();
				SqlCommand cmdGetExtProp = new SqlCommand();
				cmdGetExtProp.CommandText = String.Format("SELECT value FROM ::fn_listextendedproperty({0}, {1}, {2}, {3}, {4}, {5}, {6})", new object[] { property, userName, userValue, tableName, tableValue, columnName, columnValue });
				cmdGetExtProp.CommandType = System.Data.CommandType.Text;
				cmdGetExtProp.Connection = conn;
				externalReader = cmdGetExtProp.ExecuteReader();
				if (externalReader.Read())
				{
					if (externalReader[0] != System.DBNull.Value)
					{
						returnVal = externalReader.GetString(0);
					}
				}
			}
			catch (Exception ex)
			{
				throw ex;
			}
			finally
			{
				if (externalReader != null)
					externalReader.Close();
				if (conn != null)
					conn.Close();
			}
			return returnVal;
		}

		private static bool ExtendedPropertyExists(string connectionString, string property, string user, string table, string column)
		{
			bool retval = false;
			if (CanUseExtendedProperty(connectionString))
			{
				string userName = string.Empty;
				string userValue = string.Empty;
				string tableName = string.Empty;
				string tableValue = string.Empty;
				string columnName = string.Empty;
				string columnValue = string.Empty;

				property = "'" + property + "'";
				if (user == string.Empty)
				{
					userName = "NULL";
					userValue = "NULL";
				}
				else
				{
					userName = "'user'";
					userValue = "'" + user + "'";
				}
				if (table == string.Empty)
				{
					tableName = "NULL";
					tableValue = "NULL";
				}
				else
				{
					tableName = "'table'";
					tableValue = "'" + table + "'";
				}
				if (column == string.Empty)
				{
					columnName = "NULL";
					columnValue = "NULL";
				}
				else
				{
					columnName = "'column'";
					columnValue = "'" + column + "'";
				}

				System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection();
				System.Data.SqlClient.SqlDataReader externalReader = null;
				try
				{
					conn.ConnectionString = connectionString;
					conn.Open();
					SqlCommand command = new SqlCommand();
					command.CommandText = String.Format("SELECT value FROM ::fn_listextendedproperty({0}, {1}, {2}, {3}, {4}, {5}, {6})", new object[] { property, userName, userValue, tableName, tableValue, columnName, columnValue });
					command.CommandType = System.Data.CommandType.Text;
					command.Connection = conn;
					externalReader = command.ExecuteReader();
					if (externalReader.Read())
					{
						retval = true;
					}
				}
				catch (Exception ex)
				{
					throw ex;
				}
				finally
				{
					if (externalReader != null)
						externalReader.Close();
					if (conn != null)
						conn.Close();
				}
			}
			return retval;
		}
		#endregion

		#region private sql statement builders

		public static void RunEmbeddedFile(SqlConnection connection, SqlTransaction transaction, string resourceFileName)
		{
			RunEmbeddedFile(connection, transaction, resourceFileName, null);
		}

		public static void RunEmbeddedFile(SqlConnection connection, SqlTransaction transaction, string resourceFileName, List<string> failedScripts)
		{
			var tempFolder = string.Empty;
			//if (resourceFileName.ToLower().EndsWith(".zip"))
			//{
			//  tempFolder = ArchiveReader.ExtractArchive(resourceFileName);
			//  var files = Directory.GetFiles(tempFolder, "*.sql");
			//  var fileList = new SortedDictionary<string, string>();

			//  foreach (string file in files)
			//  {
			//    fileList.Add(file, file);
			//  }
			//  foreach (string file in fileList.Values)
			//  {
			//    var scripts = ReadSQLFileSectionsFromFile(file);
			//    foreach (string sql in scripts)
			//    {
			//      ExecuteSQL(connection, transaction, sql, failedScripts);
			//    }
			//  }

			//  //Remove the temp folder if necessary
			//  try
			//  {
			//    System.IO.Directory.Delete(tempFolder, true);
			//  }
			//  catch { }

			//}
			//else
			//{
				var scripts = ReadSQLFileSectionsFromResource(resourceFileName);
				foreach (string sql in scripts)
				{
					ExecuteSQL(connection, transaction, sql, failedScripts);
				}
			//}

		}

		internal static void ExecuteSQL(SqlConnection connection, SqlTransaction transaction, string sql)
		{
			ExecuteSQL(connection, transaction, sql, null);
		}

		internal static void ExecuteSQL(SqlConnection connection, SqlTransaction transaction, string sql, List<string> failedScripts)
		{
			sql = sql.Trim();
			if (string.IsNullOrEmpty(sql)) return;
			System.Data.SqlClient.SqlCommand command = new System.Data.SqlClient.SqlCommand(sql, connection);
			command.Transaction = transaction;
			command.CommandTimeout = 300;
			try
			{
				command.ExecuteNonQuery();
			}
			catch (System.Data.SqlClient.SqlException sqlexp)
			{
				if ((sqlexp.Number == 1779) && sql.StartsWith("--PRIMARY KEY FOR TABLE"))
				{
					//Ignore this error
					return;
				}
				else if ((sqlexp.Number == 1781) && sql.StartsWith("--DEFAULTS FOR TABLE"))
				{
					//Ignore this error
					return;
				}
				else if (failedScripts != null)
				{
					//Ignore this error, we will re-process it
					failedScripts.Add(sql);
					return;
				}
				else
					throw;
			}
			catch { throw; }
		}

		public static string[] ReadSQLFileSectionsFromResource(string resourceFileName)
		{
			ArrayList retval = new ArrayList();
			var sb = new StringBuilder();
			Assembly asm = Assembly.GetExecutingAssembly();

			System.IO.Stream manifestStream = asm.GetManifestResourceStream(resourceFileName);
			try
			{
				using (System.IO.StreamReader sr = new System.IO.StreamReader(manifestStream))
				{
					while (!sr.EndOfStream)
					{
						var lineText = sr.ReadLine();
						if (lineText.ToUpper().Trim() == "GO")
						{
							var s = sb.ToString();
							s = s.Trim();
							retval.Add(s);
							sb = new StringBuilder();
						}
						else
						{
							sb.AppendLine(lineText);
						}
					}
				}
			}
			catch { }
			finally
			{
				manifestStream.Close();
			}
			//Last string
			if (!string.IsNullOrEmpty(sb.ToString()))
				retval.Add(sb.ToString());

			return (string[])retval.ToArray(typeof(string));
		}

		private static string[] ReadSQLFileSectionsFromFile(string fileName)
		{
			var retval = new ArrayList();
			var sb = new StringBuilder();
			var asm = Assembly.GetExecutingAssembly();

			var manifestStream = File.OpenText(fileName);
			try
			{
				while (!manifestStream.EndOfStream)
				{
					var lineText = manifestStream.ReadLine();
					if (lineText.ToUpper().Trim() == "GO")
					{
						retval.Add(sb.ToString());
						sb = new StringBuilder();
					}
					else
					{
						sb.AppendLine(lineText);
					}
				}
			}
			catch { }
			finally
			{
				manifestStream.Close();
			}
			//Last string
			if (!string.IsNullOrEmpty(sb.ToString()))
				retval.Add(sb.ToString());

			return (string[])retval.ToArray(typeof(string));
		}

		private static string GetSqlForTableExtendedPropertyLikeCount(string tableName, string likeString)
		{
			var sb = new StringBuilder();
			sb.Append(" SELECT ");
			sb.Append("	count(*) ");
			sb.Append(" from ");
			sb.Append("	sysobjects so ");
			sb.Append(" inner join sysproperties sp on so.id = sp.id ");
			sb.Append(" where ");
			sb.Append(" so.name='" + tableName + "' AND ");
			sb.Append(" sp.name like '" + likeString + "' ");
			return sb.ToString();
		}

		private static string GetSqlColumnsForTable(string tableName)
		{
			var sb = new StringBuilder();
			sb.Append("SELECT DISTINCT 	\n");
			sb.Append("	colorder, \n");
			sb.Append("	syscolumns.name, 	\n");
			sb.Append("	case when primaryKey.xtype ='PK' then 'true' else 'false' end as isPrimaryKey,  	\n");
			sb.Append("	case when fk.fkey is null then 'false' else 'true' end as isForeignKey,  	\n");
			sb.Append("	systypes.name as datatype, 	\n");
			sb.Append("	syscolumns.length, 	\n");
			sb.Append("	case when syscolumns.isnullable = 0 then 'false' else 'true' end as allowNull,  	\n");
			sb.Append("	case when syscomments.text is null then '' else SUBSTRING ( syscomments.text , 2 , len(syscomments.text)-2 ) end as defaultValue, 	\n");
			sb.Append("	case when syscolumns.autoval is null then 'false' else 'true' end as isIdentity \n");
			sb.Append("FROM \n");
			sb.Append("	sysobjects 	\n");
			sb.Append("	inner join syscolumns on syscolumns.id = sysobjects.id 	\n");
			sb.Append("	inner join systypes on systypes.xtype = syscolumns.xtype 	\n");
			sb.Append("	left outer join sysindexkeys on sysindexkeys.id = syscolumns.id AND sysindexkeys.colid = syscolumns.colid 	\n");
			sb.Append("	left outer join sys.indexes pk on  pk.id = sysindexkeys.id AND pk.indid = sysindexkeys.indid 	\n");
			sb.Append("	left outer join sysobjects primaryKey on pk.name = primaryKey.name\n");
			sb.Append("	left outer join sysforeignkeys fk on fk.fkeyid = syscolumns.id AND fk.fkey = syscolumns.colorder 	\n");
			
			if (DatabaseVersion == SQLServerTypeConstants.SQL2005)
				sb.Append("	left outer join syscomments on syscolumns.cdefault = syscomments.id \n");
			else
				sb.Append("	left outer join sys.sql_expression_dependencies on syscolumns.cdefault = sys.sql_expression_dependencies.referencing_id \n");

			sb.Append("WHERE \n");
			sb.Append("	sysobjects.name = '").Append(tableName).Append("' AND systypes.name <> 'sysname' order by colorder\n");
			return sb.ToString();
		}

		private static string GetSqlForRelationships(string tableName)
		{
			var sb = new StringBuilder();
			sb.Append("SELECT DISTINCT parent.name as Parent, child.name as Child, ");
			sb.Append("case when parent.name = '").Append(tableName).Append("' then 'parent' else 'child' end as rolePlayed, ");
			sb.Append("relation.name as constraintName, ");
			sb.Append("roleNameProvider.value as roleName ");
			sb.Append("FROM sysforeignkeys inner join sysobjects relation on constid = relation.id ");
			sb.Append("inner join sysobjects child on fkeyid = child.id inner join sysobjects parent on rkeyid = parent.id ");
			sb.Append("inner join sysproperties roleNameProvider on roleNameProvider.id = relation.id ");
			sb.Append("WHERE parent.name = '" + tableName + "' OR child.name='" + tableName + "'");
			return sb.ToString();
		}

		private static string GetSqlForForeignKeys(string parentTable, string childTable, string constraintName)
		{
			var sb = new StringBuilder();
			sb.Append(" DECLARE @FKeys TABLE ");
			sb.Append(" ( ");
			sb.Append(" parentTable [Varchar] (100) NOT NULL, ");
			sb.Append(" 	childTable [Varchar] (100) NOT NULL, ");
			sb.Append(" 	childColumn [Varchar] (100) NOT NULL, ");
			sb.Append(" 	constid int NOT NULL, ");
			sb.Append(" 	keyno smallint NOT NULL ");
			sb.Append(" ) ");
			sb.Append(" DECLARE @PKeys TABLE ");
			sb.Append(" ( ");
			sb.Append(" parentTable [Varchar] (100) NOT NULL, ");
			sb.Append(" childTable [Varchar] (100) NOT NULL, ");
			sb.Append(" parentColumn [Varchar] (100) NOT NULL, ");
			sb.Append(" constid int NOT NULL, ");
			sb.Append(" 	keyno smallint NOT NULL ");
			sb.Append(" ) ");
			sb.Append(" INSERT INTO @FKeys ");
			sb.Append(" SELECT DISTINCT ");
			sb.Append(" parent.name parentTable, ");
			sb.Append(" child.name childTable, ");
			sb.Append(" syscolumns.name as childColumn, ");
			sb.Append(" sysforeignkeys.constid, ");
			sb.Append(" sysforeignkeys.keyno ");
			sb.Append(" FROM ");
			sb.Append(" sysforeignkeys ");
			sb.Append(" inner join sysobjects child on fkeyid = child.id ");
			sb.Append(" inner join sysobjects parent on rkeyid = parent.id ");
			sb.Append(" inner join syscolumns on syscolumns.id = sysforeignkeys.fkeyid AND syscolumns.colorder = sysforeignkeys.fkey ");
			sb.Append(" INSERT INTO @PKeys ");
			sb.Append(" SELECT ");
			sb.Append(" parent.name parentTable, ");
			sb.Append(" child.name childTable, ");
			sb.Append(" syscolumns.name as parentColumn, ");
			sb.Append(" sysforeignkeys.constid, ");
			sb.Append(" sysforeignkeys.keyno ");
			sb.Append(" FROM ");
			sb.Append(" sysforeignkeys inner join sysobjects child on fkeyid = child.id ");
			sb.Append(" inner join sysobjects parent on rkeyid = parent.id ");
			sb.Append(" inner join syscolumns on syscolumns.id = sysforeignkeys.rkeyid AND syscolumns.colorder = sysforeignkeys.rkey ");
			sb.Append(" SELECT p.parentTable ,p.parentColumn, f.childTable, f.ChildColumn , so.name as roleName FROM @FKeys f INNER JOIN @PKeys p on f.constid=p.constID and f.keyno=p.keyno INNER JOIN sysobjects so on so.id = p.constid ");
			sb.Append("WHERE f.parentTable = '").Append(parentTable).Append("' AND f.childTable = '").Append(childTable).Append("'");
			sb.Append(" AND so.name = '" + constraintName + "'");
			sb.Append(" order by p.constid ");
			return sb.ToString();
		}

		private static string SqlRemoveTable(string tableName)
		{
			var sb = new StringBuilder();
			sb.Append("DROP TABLE [" + tableName + "];\n");
			return sb.ToString();
		}
		#endregion

	}

	internal class HistoryItem
	{
		public DateTime PublishDate { get; set; }
		public string Version { get; set; }
	}

	internal class nHydrateSetting
	{
		internal nHydrateSetting()
		{
			this.History = new List<HistoryItem>();
			this.IsLoaded = false;
			this.dbVersion = "0.0.0.0.0";
			this.LastUpdate = new DateTime(1980, 1, 1);
		}

		public bool IsLoaded { get; private set; }

		public void Load(string connectionString)
		{
			System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection();
			try
			{
				conn.ConnectionString = connectionString;
				conn.Open();

				var da = new SqlDataAdapter("select * from sys.tables where name = '__nhydrateschema'", conn);
				var ds = new DataSet();
				da.Fill(ds);
				if (ds.Tables[0].Rows.Count > 0)
				{
					da = new SqlDataAdapter("SELECT * FROM __nhydrateschema", conn);
					ds = new DataSet();
					da.Fill(ds);
					var t = ds.Tables[0];
					this.dbVersion = (string)t.Rows[0]["dbVersion"];
					this.LastUpdate = (DateTime)t.Rows[0]["LastUpdate"];
					this.ModelKey = (Guid)t.Rows[0]["ModelKey"];
					this.LoadHistory((string)t.Rows[0]["History"]);
					this.IsLoaded = true;
				}
				else
				{
					this.dbVersion = SqlServers.SelectExtendedProperty(connectionString, "dbVersion", string.Empty, string.Empty, string.Empty);
					try
					{
						this.LastUpdate = DateTime.ParseExact(SqlServers.SelectExtendedProperty(connectionString, "LastUpdate", string.Empty, string.Empty, string.Empty), "yyyy-MM-dd HH:mm:ss", System.Globalization.CultureInfo.InvariantCulture);
					}
					catch { }

					try
					{
						this.ModelKey = new Guid(SqlServers.SelectExtendedProperty(connectionString, "ModelKey", string.Empty, string.Empty, string.Empty));
					}
					catch { }

					this.LoadHistory(SqlServers.SelectExtendedProperty(connectionString, "History", string.Empty, string.Empty, string.Empty));
				}
			}
			catch (Exception ex)
			{
				throw;
			}
			finally
			{
				if (conn != null)
					conn.Close();
			}
		}

		public bool Save(string connectionString)
		{
			var sb = new StringBuilder();
			sb.AppendLine("if not exists(select * from sysobjects where name = '__nhydrateschema' and xtype = 'U')");
			sb.AppendLine("BEGIN");
			sb.AppendLine("CREATE TABLE [__nhydrateschema] (");
			sb.AppendLine("[dbVersion] [varchar] (50) NOT NULL,");
			sb.AppendLine("[LastUpdate] [datetime] NOT NULL,");
			sb.AppendLine("[ModelKey] [uniqueidentifier] NOT NULL,");
			sb.AppendLine("[History] [varchar](max) NOT NULL");
			sb.AppendLine(")");
			sb.AppendLine("--PRIMARY KEY FOR TABLE");
			sb.AppendLine("if not exists(select * from sysobjects where name = '__pk__nhydrateschema' and xtype = 'PK')");
			sb.AppendLine("ALTER TABLE [__nhydrateschema] WITH NOCHECK ADD CONSTRAINT [__pk__nhydrateschema] PRIMARY KEY CLUSTERED ([ModelKey])");
			sb.AppendLine("END");

			//Enter data
			sb.AppendLine("if exists(select * from [__nhydrateschema] where [ModelKey] = '" + this.ModelKey.ToString() + "')");
			sb.AppendLine("UPDATE [__nhydrateschema] SET [dbVersion]=@dbVersion, [LastUpdate]=@LastUpdate, [History]=@History WHERE [ModelKey] = '" + this.ModelKey.ToString() + "'");
			sb.AppendLine("ELSE");
			sb.AppendLine("INSERT INTO [__nhydrateschema] ([dbVersion], [LastUpdate], [ModelKey], [History]) VALUES (@dbVersion, @LastUpdate, @ModelKey, @History)");

			System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection();
			try
			{
				conn.ConnectionString = connectionString;
				conn.Open();
				var command = new SqlCommand(sb.ToString(), conn);
				command.Parameters.Add(new SqlParameter("dbVersion", this.dbVersion));
				command.Parameters.Add(new SqlParameter("LastUpdate", this.LastUpdate));
				command.Parameters.Add(new SqlParameter("ModelKey", this.ModelKey.ToString()));
				command.Parameters.Add(new SqlParameter("History", this.ToHistoryString()));
				command.ExecuteNonQuery();
				return true;
			}
			catch (Exception ex)
			{
				return false;
				throw;
			}
			finally
			{
				if (conn != null)
					conn.Close();
			}

		}


		public string dbVersion { get; set; }
		public List<HistoryItem> History { get; private set; }
		public DateTime LastUpdate { get; set; }
		public Guid ModelKey { get; set; }

		public void LoadHistory(string text)
		{
			this.History.Clear();
			if (!string.IsNullOrEmpty(text))
			{
				foreach (string dataRow in text.Split('^'))
				{
					string[] data = dataRow.Split('|');
					if (data.Length == 2)
					{
						try
						{
							this.History.Add(new HistoryItem() { PublishDate = DateTime.ParseExact(data[0], "yyyy-MM-dd HH:mm:ss", CultureInfo.InvariantCulture), Version = data[1] });
						}
						catch { }
					}
				}
			}
		}

		public string ToHistoryString()
		{
			var sb = new StringBuilder();
			foreach (var o in this.History)
			{
				sb.Append(o.PublishDate.ToString("yyyy-MM-dd HH:mm:ss") + "|" + o.Version + "^");
			}
			var s = sb.ToString();
			if (s.Length > 0) s = s.TrimEnd(new char[] { '^' });
			return s;
		}
	}

}